package edu.ccut.saturn.basecomponent.database.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import edu.ccut.saturn.basecomponent.database.IInsertSQLBuilder;
import edu.ccut.saturn.basecomponent.database.ISaturnSQL;
import edu.ccut.saturn.basecomponent.database.util.SaturnDataUtil;
import edu.ccut.saturn.basecomponent.database.util.SaturnSQLUtil;
import edu.ccut.saturn.component.IDataInfo;
import edu.ccut.saturn.component.IDataItem;
import edu.ccut.saturn.component.SaturnComponentException;
import edu.ccut.saturn.component.SaturnData;

public abstract class AbstractInsertSQLBuilder extends AbstractSQLBuilder
		implements IInsertSQLBuilder {

	protected final String TRUE = "true";

	public ISaturnSQL build(SaturnData saturnData, Connection connection)
			throws Exception {

		StringBuffer sql = new StringBuffer();

		StringBuffer sqlAppend = new StringBuffer();
		// 此处oracle/db2需提前设值 mysql/sqlserver则重写此方法,无需判断.考虑添加触发器,达成统一
		setPrimerKey(saturnData, connection);

		IDataInfo dataInfo = saturnData.getSaturnDataInfo();

		String tableName = SaturnDataUtil.getTableName(dataInfo);

		sql.append("INSERT INTO ").append(tableName).append(" (");
		// key在saturndata中是name属性
		for (String key : dataInfo.keySet()) {

			IDataItem iDataItem = dataInfo.getDataItem(key);
			// 为列名
			String colum = iDataItem.getColumn();
			// 当saturndata中有当前字段值得时候，插入不需要使用别名
			// 判断是否有表名,如果没有则此列不被添加到sql语句中
			// object不插 null ,basetype 不插 0
			if (iDataItem.getTableName() != null
					&& !"".equals(iDataItem.getTableName().trim())) {
				// 为它在saturndata中的值
				Object value = saturnData.get(key);
				// value为null或者是数字类型其值为0考虑saturndata中如果有数字类型不输入,则存入的值为null,完全不必考虑是否为0,建议去掉
				// &&!isZero(value)
				if (!SaturnSQLUtil.isNull(value)) {
					// 判断是否是主键mysql,sqlserver需判断,3种情况需要考虑:1是主键,自动增长2是主键,没有自动增长3不是主键
					// mysql/sqlserver情況一样,1不需要传入值value=null,2需要传入值,值不为null,3.参考普通列
					// oracle/db2 情况一样,1.其值不为null 2.其值不为null 3.参考普通列
					// 综上 都无需考虑是否是主键
					// if (!isPrimaryKey(iDataItem,value)) {
					sql.append(colum).append(", ");
					// value被传入,则必不为null
					sqlAppend.append(this
							.getColumnValueString(iDataItem, value));
					// }
				}
			}

		}
		if (!sql.toString().contains(",")) {
			throw new SaturnComponentException(
					"Can't Insert cause it is a empty SaturnData [" + saturnData
							+ "]");
		}
		this.removeLastQuote(sql);
		this.removeLastQuote(sqlAppend);

		sql.append(") values ( ").append(sqlAppend).append(")");

		SaturnSQL saturnsql = new SaturnSQL();
		saturnsql.setSql(sql.toString());
		saturnsql.setDBtype(this.getDatabaseType());
		saturnsql.setSaturnData(saturnData);
		saturnsql.setSaturnDataFullKey(dataInfo.getDataFullKey());
		saturnsql.setOperationType(this.getOperationType());

		return saturnsql;
	}

	/**
	 * 判断是否为0,oracle中数值如果为0则会默认为其赋值为0
	 * 
	 * @author zjh
	 * 
	 */
	protected boolean isZero(Object value) {
		if (value instanceof Double || value instanceof Integer
				|| value instanceof Float || value instanceof Short
				|| value instanceof Long) {

			if (value.equals(0)) {
				return true;
			}
		}
		return false;
	}

	/**
	 * 判断是否是主键,oracle中主键作为普通字段被插入
	 * 
	 * @author zjh
	 * 
	 */
	protected boolean isPrimaryKey(IDataItem dataItem, Object value) {
		return false;

	}

	/**
	 * 获取SQL语句中values后部分
	 * 
	 * @param iDataItem
	 * @param value
	 * @return
	 */
	protected abstract String getColumnValueString(IDataItem iDataItem,
			Object value);

	/**
	 * 得到查询主键值的sql语句
	 * 
	 * @author zjh
	 * 
	 */
	protected abstract String getPrimerKeySql(IDataItem dataItem);

	/**
	 * oracle中通过sequence设置主键
	 * 
	 * @author zjh
	 * 
	 */
	protected void setPrimerKey(SaturnData saturnData, Connection conn)
			throws SaturnComponentException {

		IDataInfo dataInfo = saturnData.getSaturnDataInfo();

		for (String key : dataInfo.keySet()) {
			IDataItem iDataItem = dataInfo.getDataItem(key);

			if ("true".equals(iDataItem.getIsPrimaryKey())) {
				// 没有赋值才生成主键
				if (saturnData.get(key) == null) {

					Statement stat = null;
					ResultSet rs = null;
					try {
						String sql = getPrimerKeySql(iDataItem);

						stat = conn.createStatement();
						rs = stat.executeQuery(sql);
						if (rs.next()) {
							String primaryKeyValue = rs.getString("primaryKey");

							String type = iDataItem.getJavaType();

							if (Integer.class.getName().equals(type)) {
								saturnData
										.set(key, new Integer(primaryKeyValue));
							} else if (String.class.getName().equals(type)) {
								saturnData.set(key, primaryKeyValue);
							} else if (BigDecimal.class.getName().equals(type)) {
								saturnData.set(key, new BigDecimal(
										primaryKeyValue));
							} else {
								throw new SaturnComponentException(
										"primarykey javaType:" + type
												+ " unsupport");
							}
						}
					} catch (Exception e) {
						e.printStackTrace();
						throw new SaturnComponentException(
								"primary key create exception");
					} finally {
						if (rs != null) {
							try {
								rs.close();
							} catch (SQLException e) {
								e.printStackTrace();
							}
						}
						if (stat != null) {
							try {
								stat.close();
							} catch (SQLException e) {
								e.printStackTrace();
							}
						}
					}

					break;
				}
			}
		}
	}

	/**
	 * 该方法移除StringBuff中最后多出的","
	 * 
	 * @author zjh
	 * 
	 */
	private void removeLastQuote(StringBuffer buffer) {
		int index = buffer.lastIndexOf(",");

		buffer.delete(index, buffer.length());
	}

}
